package Laborum2.repository;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;


import Laborum2.Basicas.*;


@Component
public class JdbcLaboralRepository implements LaboralRepository{
	
	List <Laboral> lab = null;
	Laboral laboral =null;
	
	@Autowired
	private DataSource dataSource;
	

	private void closeConecction(ResultSet rs, PreparedStatement ps,
			Connection conn) {

		if (rs != null) {
			try {
				// Close to prevent database cursor exhaustion
				rs.close();
			} catch (SQLException ex) {
			}
		}
		if (ps != null) {
			try {
				// Close to prevent database cursor exhaustion
				ps.close();
			} catch (SQLException ex) {
			}
		}
		if (conn != null) {
			try {
				// Close to prevent database connection exhaustion
				conn.close();
			} catch (SQLException ex) {
			}
		}

	}

	private void closeConecction1(PreparedStatement ps, Connection conn) {

		if (ps != null) {
			try {
				// Close to prevent database cursor exhaustion
				ps.close();
			} catch (SQLException ex) {
			}
		}
		if (conn != null) {
			try {
				// Close to prevent database connection exhaustion
				conn.close();
			} catch (SQLException ex) {
			}
		}

	}

	
	public List<Laboral> obtenerLaboralDNI(int id,int dni) {
	
      String sql = "SELECT * from Laboral where id=? and dni=? and estado =1 " ;
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = dataSource.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.setInt(2, dni);
			rs = ps.executeQuery();

			while (rs.next()) {
				laboral.setId(rs.getInt("id"));
				laboral.setDni(rs.getInt("dni"));
				laboral.setEmpresa(rs.getString("empresa"));
			    laboral.setCargo(rs.getInt("cargo"));
			 	laboral.setDescripcion(rs.getString("descripcion"));
				laboral.setFecini(rs.getDate("fecini"));
		     	laboral.setFecfin(rs.getDate("fecfin"));
				laboral.setReferencia(rs.getString("referencia"));
		    	laboral.setTele(rs.getInt("tele"));
				laboral.setCorreo(rs.getString("correo"));
				
				
			System.out.println("fecha inicio " + laboral.getDescripcion());	
				
	           
	        	 lab.add(laboral);
              
				
			}
			return lab;
		} catch (SQLException ex) {

		}
		closeConecction(rs, ps, conn);
				return lab;
	}

   public void editar(Laboral laboral){

	   if (obtenerLaboralDNI1(laboral.getDni()).size() > 0) {

			String sql = "update Laboral set dni=?,empresa=?,cargo=?,descripcion=?,fecini=?,fecfin=?,referencia=?,tele=?,correo=? " +
					"where dni=? and id=?";

			Connection conn = null;
			PreparedStatement ps = null;

			try {
				conn = dataSource.getConnection();
				ps = conn.prepareStatement(sql);
				ps.setInt(1, laboral.getDni());
				ps.setString(2, laboral.getEmpresa());
				ps.setInt(3, laboral.getCargo());
				ps.setString(4, laboral.getDescripcion());
	        	ps.setDate(5, (Date) laboral.getFecini());
				ps.setDate(6, (Date) laboral.getFecfin());
				ps.setString(7, laboral.getReferencia());
				ps.setInt(8, laboral.getTele());
				ps.setString(9, laboral.getCorreo());
				ps.setInt(10, laboral.getDni());
				ps.setInt(11, laboral.getId());
						ps.execute();
			} catch (SQLException ex) {
			}
			closeConecction1(ps, conn);


	   }
}


	public void eliminar(int id,int dni) {
		
		String sql = "update Laboral set estado=0 where id=? and dni=?";

		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = dataSource.getConnection();
			ps = conn.prepareStatement(sql);
		
			ps.setInt(1, id);
			ps.setInt(2, dni);
			
			ps.execute();
		} catch (SQLException ex) {
		}
		closeConecction1(ps, conn);

}


	public void ingresar(Laboral laboral) {
		
		
		String sql = "insert into Laboral (dni,empresa,cargo,descripcion,fecini,fecfin,referencia,tele,correo,estado) " +
				                  "values (?,?,?,?,?,?,?,?,?,?)";

		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = dataSource.getConnection();
			ps = conn.prepareStatement(sql);
			
			ps.setInt(1, laboral.getDni());
			ps.setString(2, laboral.getEmpresa());
			ps.setInt(3, laboral.getCargo());
			ps.setString(4, laboral.getDescripcion());
       		ps.setDate(5, (Date) laboral.getFecini());
			ps.setDate(6, (Date) laboral.getFecfin());
			ps.setString(7, laboral.getReferencia());
			ps.setInt(8, laboral.getTele());
			ps.setString(9, laboral.getCorreo());
			ps.setInt(10, laboral.getEstado());
			ps.execute();
			
		
		

		} catch (SQLException ex) {
		}
		closeConecction1(ps, conn);
	}

	@Override
	public List<Laboral> obtenerLaboralDNI1(int dni) {
		List <Laboral> lab = new ArrayList <Laboral>();
		Laboral laboral =new Laboral();
	
		 String sql = "SELECT * from Laboral where dni=? and estado=1";
			
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
		
			try {
				conn = dataSource.getConnection();
				ps = conn.prepareStatement(sql);
				ps.setInt(1, dni);
			
				rs = ps.executeQuery();
              
				while (rs.next()) {
					laboral=new Laboral();
					laboral.setId(rs.getInt("id"));
					laboral.setDni(rs.getInt("dni"));
					laboral.setEmpresa(rs.getString("empresa"));
				    laboral.setCargo(rs.getInt("cargo"));
				 	laboral.setDescripcion(rs.getString("descripcion"));
					laboral.setFecini(rs.getDate("fecini"));
			     	laboral.setFecfin(rs.getDate("fecfin"));
					laboral.setReferencia(rs.getString("referencia"));
			     	laboral.setTele(rs.getInt("tele"));
					laboral.setCorreo(rs.getString("correo"));
					laboral.setEstado(rs.getInt("estado"));
				         
					System.out.println("fecha inicio " + laboral.getEmpresa()+ " "+laboral.getId()+ " "+ laboral.getId());	
						
		        	 lab.add(laboral);
		        	 
	              
				}
	           
			} catch (SQLException ex) {

			}
			closeConecction(rs, ps, conn);
					return lab;

	} 

	
		
	}




